/*
SELECT * FROM FN_QAD_SEARCH_RESULTS( '0', 0, 0, '0', '0', '0', '0', '0', '0' )
*/
ALTER FUNCTION FN_QAD_SEARCH_RESULTS
(
	@PS_TYPE_NAME 		VARCHAR(50),
	@BASE_PRODUCT_ID	INT,
	@PROCESSSTEP_ID 	INT,
	@CARD_TYPE		VARCHAR(50),
	@PACKAGE_TYPE		VARCHAR(50),
	@MEMORY_SIZE		VARCHAR(50),
	@DATE_TYPE		VARCHAR(20),
	@FROM_DATE		VARCHAR(20),
	@TO_DATE		VARCHAR(20)
)
RETURNS @FN_RESULTS TABLE (ITEM_NUMBER VARCHAR(50), ITEM_DESC VARCHAR(50), WORK_ORDER_NO VARCHAR(50), WORK_ORDER_ID INT, QTY_COMPLETED INT,
			QTY_REJECTED INT, QTY_OPEN INT, ORD_DATE DATETIME, REL_DATE DATETIME, DUE_DATE DATETIME, WO_STATUS VARCHAR(50),	ADAPTOR_DATE DATETIME, TIME_STAMP DATETIME )
AS
BEGIN
	
	DECLARE @PROCESS_STEPS_TABLE TABLE ( PROCESSSTEP_ID INT )
	DECLARE @PROCESS_PARAMETER_PS_TABLE1 TABLE ( CARD_PROCESSSTEP_ID INT )
 	DECLARE @PROCESS_PARAMETER_PS_TABLE2 TABLE ( PKG_PROCESSSTEP_ID INT )
	DECLARE @PROCESS_PARAMETER_PS_TABLE3 TABLE ( MEMORY_PROCESSSTEP_ID INT )
	
	DECLARE @MAX_DATE DATETIME
	DECLARE @MIN_DATE DATETIME
	
	DECLARE @DATE_TO DATETIME
	DECLARE @DATE_FROM DATETIME
	
	IF @DATE_TYPE = 'byDueDate'
	BEGIN
		SELECT @MAX_DATE = MAX( DUE_DATE ) FROM QAD_TABLE
		SELECT @MIN_DATE = MIN( DUE_DATE ) FROM QAD_TABLE
	END
	ELSE IF @DATE_TYPE = 'byRelDate'
	BEGIN
		SELECT @MAX_DATE = MAX( REL_DATE ) FROM QAD_TABLE
		SELECT @MIN_DATE = MIN( REL_DATE ) FROM QAD_TABLE
	END
	
	IF ( @FROM_DATE IS NULL OR @FROM_DATE = '') 
	BEGIN
		SET @FROM_DATE = '0'
	END
	IF ( @TO_DATE IS NULL OR @TO_DATE = '') 
	BEGIN
		SET @TO_DATE = '0'
	END
	
	IF ( @FROM_DATE = '0') AND (@TO_DATE = '0') 
	BEGIN
		SET @DATE_TO = @MAX_DATE
		SET @DATE_FROM = @MIN_DATE
	END
	ELSE IF (@FROM_DATE <> '0') AND (@TO_DATE <> '0') 
	BEGIN
		SET @DATE_TO = @TO_DATE
		SET @DATE_FROM = @FROM_DATE
	END	
	ELSE IF (@FROM_DATE = '0') AND (@TO_DATE <> '0') 
	BEGIN
		SET @DATE_TO = @TO_DATE
		SET @DATE_FROM = @MIN_DATE
	END 
 	ELSE IF (@FROM_DATE <> '0') AND (@TO_DATE = '0') 
	BEGIN
		SET @DATE_TO = @MAX_DATE
		SET @DATE_FROM = @FROM_DATE	
	END
	ELSE
	BEGIN
		SET @DATE_TO = @MAX_DATE
		SET @DATE_FROM = @MIN_DATE
	END
	
	IF ( @PROCESSSTEP_ID <> 0 )
	BEGIN
		INSERT INTO @PROCESS_STEPS_TABLE( PROCESSSTEP_ID ) VALUES ( @PROCESSSTEP_ID )		
	END
	ELSE
	BEGIN
		IF ( @BASE_PRODUCT_ID <> 0 AND @PS_TYPE_NAME <> '0' )
		BEGIN
			INSERT INTO @PROCESS_STEPS_TABLE( PROCESSSTEP_ID ) SELECT P.PROCESSSTEP_ID FROM PRODUCT P, PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND P_PARENT_PRODUCT_ID = @BASE_PRODUCT_ID AND PS_TYPE_NAME = @PS_TYPE_NAME
		END
		ELSE IF ( @BASE_PRODUCT_ID = 0 AND @PS_TYPE_NAME <> '0' )
		BEGIN
			INSERT INTO @PROCESS_STEPS_TABLE( PROCESSSTEP_ID ) SELECT P.PROCESSSTEP_ID FROM PRODUCT P, PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS_TYPE_NAME =  @PS_TYPE_NAME
		END
		ELSE
		BEGIN
			INSERT INTO @PROCESS_STEPS_TABLE( PROCESSSTEP_ID ) SELECT DISTINCT PROCESSSTEP_ID FROM PRODUCT	
		END	
	END
	
	IF ( @CARD_TYPE <> '0' )
	BEGIN
		INSERT INTO @PROCESS_PARAMETER_PS_TABLE1(CARD_PROCESSSTEP_ID) SELECT DISTINCT PROCESSSTEP_ID FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'CARD TYPE' AND PARAMETER_VALUE = @CARD_TYPE 
	END
	IF ( @PACKAGE_TYPE <> '0' )
	BEGIN
		INSERT INTO @PROCESS_PARAMETER_PS_TABLE2(PKG_PROCESSSTEP_ID) SELECT DISTINCT PROCESSSTEP_ID FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'PACKAGE ID' AND PARAMETER_VALUE = @PACKAGE_TYPE				
	END
	IF ( @MEMORY_SIZE <> '0' )
	BEGIN
		INSERT INTO @PROCESS_PARAMETER_PS_TABLE3(MEMORY_PROCESSSTEP_ID) SELECT DISTINCT PROCESSSTEP_ID FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'MEMORY SIZE' AND PARAMETER_VALUE = @MEMORY_SIZE			
	END
	
	IF( ( SELECT COUNT(*) FROM @PROCESS_PARAMETER_PS_TABLE1 ) > 0 ) 
 	BEGIN
		DELETE FROM @PROCESS_STEPS_TABLE WHERE PROCESSSTEP_ID NOT IN ( SELECT DISTINCT CARD_PROCESSSTEP_ID FROM @PROCESS_PARAMETER_PS_TABLE1 )			
	END
	ELSE IF ( @CARD_TYPE <> '0' )-- When the ProcessStep Id is NULL
	BEGIN
		DELETE FROM @PROCESS_STEPS_TABLE
	END
	
	IF ( ( SELECT COUNT(*) FROM @PROCESS_PARAMETER_PS_TABLE2 ) > 0 )
	BEGIN
		DELETE FROM @PROCESS_STEPS_TABLE WHERE PROCESSSTEP_ID NOT IN ( SELECT DISTINCT PKG_PROCESSSTEP_ID FROM @PROCESS_PARAMETER_PS_TABLE2 )
	END
	ELSE IF ( @PACKAGE_TYPE <> '0' )-- When the ProcessStep Id is NULL
	BEGIN
		DELETE FROM @PROCESS_STEPS_TABLE
	END
	
	IF ( ( SELECT COUNT(*) FROM @PROCESS_PARAMETER_PS_TABLE3 ) > 0 )
	BEGIN
		DELETE FROM @PROCESS_STEPS_TABLE WHERE PROCESSSTEP_ID NOT IN ( SELECT DISTINCT MEMORY_PROCESSSTEP_ID FROM @PROCESS_PARAMETER_PS_TABLE3 )	
	END
	ELSE IF ( @MEMORY_SIZE <> '0' )-- When the ProcessStep Id is NULL
	BEGIN
		DELETE FROM @PROCESS_STEPS_TABLE
	END
	
	IF @DATE_TYPE = 'byDueDate'
	BEGIN
		INSERT INTO @FN_RESULTS	SELECT	ITEM_NUMBER, ITEM_DESC, WORK_ORDER_NO, WORK_ORDER_ID, QTY_COMPLETED, QTY_REJECTED, QTY_OPEN,
					    	ORD_DATE, REL_DATE, DUE_DATE, WO_STATUS, ADAPTOR_DATE, TIME_STAMP
					FROM 
						QAD_TABLE QAD, LOT_TABLE LT, PRODUCT P, @PROCESS_STEPS_TABLE PST
					WHERE 
						QAD.WORK_ORDER_NO = LT.LOT_NUMBER
						AND QAD.ITEM_NUMBER = P.PART_NUMBER
						AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID 
						AND P.PROCESSSTEP_ID = PST.PROCESSSTEP_ID
						AND DUE_DATE >=  CONVERT( VARCHAR, CONVERT( DATETIME, @DATE_FROM ) ) AND DUE_DATE  <=  CONVERT( VARCHAR, CONVERT( DATETIME, @DATE_TO ) )
	END
	ELSE IF @DATE_TYPE = 'byRelDate'
	BEGIN
		INSERT INTO @FN_RESULTS	SELECT	ITEM_NUMBER, ITEM_DESC, WORK_ORDER_NO, WORK_ORDER_ID, QTY_COMPLETED, QTY_REJECTED, QTY_OPEN,
					    	ORD_DATE, REL_DATE, DUE_DATE, WO_STATUS, ADAPTOR_DATE, TIME_STAMP
					FROM 
						QAD_TABLE QAD, LOT_TABLE LT, PRODUCT P, @PROCESS_STEPS_TABLE PST
					WHERE 
						QAD.WORK_ORDER_NO = LT.LOT_NUMBER
						AND QAD.ITEM_NUMBER = P.PART_NUMBER
						AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID 
						AND P.PROCESSSTEP_ID = PST.PROCESSSTEP_ID
						AND REL_DATE >=  CONVERT( VARCHAR, CONVERT( DATETIME, @DATE_FROM ) ) AND REL_DATE  <=  CONVERT( VARCHAR, CONVERT( DATETIME, @DATE_TO ) )
	END
	ELSE
	BEGIN
		INSERT INTO @FN_RESULTS	SELECT	ITEM_NUMBER, ITEM_DESC, WORK_ORDER_NO, WORK_ORDER_ID, QTY_COMPLETED, QTY_REJECTED, QTY_OPEN,
					    	ORD_DATE, REL_DATE, DUE_DATE, WO_STATUS, ADAPTOR_DATE, TIME_STAMP
					FROM 
						QAD_TABLE QAD, LOT_TABLE LT, PRODUCT P, @PROCESS_STEPS_TABLE PST
					WHERE 
						QAD.WORK_ORDER_NO = LT.LOT_NUMBER
						AND QAD.ITEM_NUMBER = P.PART_NUMBER
						AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID 
						AND P.PROCESSSTEP_ID = PST.PROCESSSTEP_ID
	END		

	RETURN 
END